package com.fin.zw.intelligentbot.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 处理上传的文件
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static List<Map<String,String>> getBankListByExcel(File file) throws Exception {
        List<Map<String,String>> list = new ArrayList<>();
        InputStream in = new FileInputStream(file);
        String fileName = file.getName();
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = work.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        //添加人员的模板表头有两行
        if(rows > 2) {
            if (sheet != null) {
                for (int i = 2; i < rows; i++) {
                    Row row = sheet.getRow(i);
                    Map<String,String> map = new HashMap<>();
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        Row head = sheet.getRow(1);
                        String key = head.getCell(y).toString();

                        Cell cell = row.getCell(y);
                        String value = cell.toString();
                        System.out.println("key值是：" + key + "value值是：" + value);
                        map.put(key,value);
                    }
                    list.add(map);
                }
            }
        }
        work.close();
        return list;
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }

    public static void main(String[] args) throws Exception{
        File file = new File("D:\\Desktop\\测试文件.xlsx");
        getBankListByExcel(file);
    }
}
